package xyz.zdsoft.personal_info_madule.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import xyz.zdsoft.personal_info_madule.vo.ClassHourOfTeamVo;
import xyz.zdsoft.personal_info_madule.vo.ProjectScoreVo;
import xyz.zdsoft.personal_info_madule.vo.StuInfoVo;
import xyz.zdsoft.personal_info_madule.vo.TeamRank;

import java.util.List;
import java.util.Map;

@Mapper
public interface StudentInfoMapper {

    /**
     * 查询当前学生的个人基本信息
     * @param stuId 学生id
     * @return 基本信息
     */
    @Select("SELECT DISTINCT\n" +
            "   stu_name, user_no, id_no, sex, email,\n" +
            "   mobile_no, logo_url, colleges_name, major_name,\n" +
            "   grade_name, class_name, tea_name, teacher_mobile_no \n" +
            "FROM stu_info_view siv\n" +
            "WHERE siv.stu_id = #{stuId}")
    StuInfoVo queryStuInfo(@Param("stuId") Integer stuId);

    /**
     * 查询当前用户工号、姓名和头像
     * @param uid 当前用户id
     * @return 工号、姓名、头像
     */
    @Select("SELECT user_no, `name`, logo_url \n" +
            "FROM sys_user \n" +
            "WHERE id = #{uid}")
    Map<String, String> queryCurNoAndLogo(@Param("uid") Integer uid);

    /**
     * 更新头像地址
     * @param logoUrl 头像地址
     * @param updateBy 更信人
     * @param stuId 用户id
     * @return 影响条数
     */
    @Update("UPDATE sys_user SET logo_url=#{logoUrl}, update_by=#{updateBy}" +
            "WHERE id = #{stuId}")
    int changeFace(@Param("logoUrl") String logoUrl, @Param("updateBy") String updateBy,
                   @Param("stuId") Integer stuId);

    /**
     * 修改邮箱
     * @param email 新邮箱
     * @param stuId 学生id
     * @return 影响条数
     */
    @Update("UPDATE sys_user SET email=#{email}" +
            "WHERE id = #{stuId}")
    int changeEmail(@Param("email") String email, @Param("stuId") Integer stuId);

    /**
     * 修改电话号码
     * @param phone 新电话号码
     * @param stuId 学生id
     * @return 影响条数
     */
    @Update("UPDATE sys_user SET mobile_no=#{phone}" +
            "WHERE id = #{stuId}")
    int changePhone(@Param("phone") String phone, @Param("stuId") Integer stuId);

    /**
     * 查询小组成员的学时情况
     * @param stuId 当前学生id
     * @return 小组成员的情况
     */
    @Select("SELECT DISTINCT\n" +
            "\tteam_name,\n" +
            "\tstu.`name` stu_name,\n" +
            "\tstu.position,\n" +
            "\ttmi.is_team_leader,\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tSUM(\n" +
            "\t\tTRUNCATE (( TIMESTAMPDIFF( MINUTE, cpi.sign_in_time, cpi.sign_out_time ) / 60 ), 2 )) \n" +
            "\tFROM\n" +
            "\t\tcourse_student cs\n" +
            "\t\tLEFT JOIN course_project_info cpi ON cs.course_project_info_id = cpi.id \n" +
            "\t\tAND cs.student_id = stu.id \n" +
            "\t) standard,\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tSUM(\n" +
            "\t\tTRUNCATE (( TIMESTAMPDIFF( SECOND, a.real_start_time, a.real_end_time ) / 3600 ), 2 )) \n" +
            "\tFROM\n" +
            "\t\tattendance a \n" +
            "\tWHERE\n" +
            "\t\ta.student_id = stu.id \n" +
            "\t\tAND DATE( a.real_start_time ) IN (\n" +
            "\t\tSELECT DISTINCT\n" +
            "\t\t\tcpi.project_date \n" +
            "\t\tFROM\n" +
            "\t\t\tcourse_student cs\n" +
            "\t\t\tLEFT JOIN course_project_info cpi ON cs.course_project_info_id = cpi.id \n" +
            "\t\t\tAND cs.student_id = stu.id \n" +
            "\t\t) \n" +
            "\t\tAND a.attendance_type = '1' \n" +
            "\t) main,\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tSUM( daytime ) \n" +
            "\tFROM\n" +
            "\t\t(\n" +
            "\t\tSELECT DISTINCT TRUNCATE\n" +
            "\t\t\t( TIMESTAMPDIFF( SECOND, MIN( real_start_time ), MAX( real_end_time ))/ 3600, 2 ) daytime \n" +
            "\t\tFROM\n" +
            "\t\t\tattendance a \n" +
            "\t\tWHERE\n" +
            "\t\t\ta.student_id = stu.id \n" +
            "\t\tGROUP BY\n" +
            "\t\tDATE( a.real_start_time )) dayday \n" +
            "\t\t) - (\n" +
            "\tSELECT\n" +
            "\t\tSUM(\n" +
            "\t\tTRUNCATE (( TIMESTAMPDIFF( SECOND, a.real_start_time, a.real_end_time ) / 3600 ), 2 )) \n" +
            "\tFROM\n" +
            "\t\tattendance a \n" +
            "\tWHERE\n" +
            "\t\ta.student_id = stu.id \n" +
            "\t\tAND DATE( a.real_start_time ) IN (\n" +
            "\t\tSELECT DISTINCT\n" +
            "\t\t\tcpi.project_date \n" +
            "\t\tFROM\n" +
            "\t\t\tcourse_student cs\n" +
            "\t\t\tLEFT JOIN course_project_info cpi ON cs.course_project_info_id = cpi.id \n" +
            "\t\t\tAND cs.student_id = stu.id \n" +
            "\t\t) \n" +
            "\t\tAND a.attendance_type = '1' \n" +
            "\t) self,\n" +
            "\tstu.mobile_no \n" +
            "FROM\n" +
            "\tteam_info ti\n" +
            "\tLEFT JOIN team_member_info tmi ON ti.id = tmi.team_info_id\n" +
            "\tLEFT JOIN sys_user stu ON tmi.student_id = stu.id \n" +
            "WHERE\n" +
            "\tti.id = ( SELECT DISTINCT tmi.team_info_id FROM team_member_info tmi WHERE tmi.student_id = 4 AND tmi.del_flag = '0' ) \n" +
            "\tAND tmi.del_flag = '0' \n" +
            "ORDER BY\n" +
            "\tstu_name")
    List<ClassHourOfTeamVo> queryTeamClassHour(@Param("stuId") Integer stuId);

    /**
     * 查询学生的项目得分
     * @param stuId 学生id
     * @return 项目得分记录
     */
    @Select("SELECT DISTINCT\n" +
            "\tci.course_name,\n" +
            "\tti.team_name,\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tsc.score \n" +
            "\tFROM\n" +
            "\t\tcomprehensive_score sc \n" +
            "\tWHERE\n" +
            "\t\tsc.course_id = ci.id \n" +
            "\t\tAND sc.student_id = stu.id \n" +
            "\t\tAND sc.team_id = ti.id \n" +
            "\t) stu_score,\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tsc.score \n" +
            "\tFROM\n" +
            "\t\tcomprehensive_score sc \n" +
            "\tWHERE\n" +
            "\t\tsc.course_id = ci.id \n" +
            "\t\tAND sc.team_id = ti.id \n" +
            "\t\tAND sc.student_id IS NULL \n" +
            "\t) team_score,\n" +
            "\tci.id course_id,\n" +
            "\tti.id team_id \n" +
            "FROM\n" +
            "\tsys_user stu\n" +
            "\tLEFT JOIN course_student cs ON stu.id = cs.student_id\n" +
            "\tLEFT JOIN course_project_info cpi ON cs.course_project_info_id = cpi.id\n" +
            "\tLEFT JOIN course_info ci ON ci.id = cpi.course_info_id\n" +
            "\tLEFT JOIN team_member_info tmi ON tmi.student_id = stu.id\n" +
            "\tLEFT JOIN team_info ti ON tmi.team_info_id = ti.id \n" +
            "WHERE\n" +
            "\tstu.id = #{stuId} \n" +
            "\tAND ti.del_flag = '0'")
    List<ProjectScoreVo> queryTeamScoreForCou(@Param("stuId") Integer stuId);

    /**
     * 查询这门课程的小组综合评分是否做完
     * @param couId 课程id
     * @return 0 未评完 1 评完
     */
    @Select("SELECT\n" +
            "\tcount(*)=(\n" +
            "\tSELECT\n" +
            "\t\tcount(*) \n" +
            "\tFROM\n" +
            "\t\t(\n" +
            "\t\tSELECT DISTINCT\n" +
            "\t\t\tti.id \n" +
            "\t\tFROM\n" +
            "\t\t\tcourse_info ci\n" +
            "\t\t\tLEFT JOIN course_project_info cpi ON ci.id = cpi.course_info_id\n" +
            "\t\t\tLEFT JOIN course_student cs ON cpi.id = cs.course_project_info_id\n" +
            "\t\t\tLEFT JOIN team_member_info tmi ON tmi.student_id = cs.student_id\n" +
            "\t\t\tLEFT JOIN team_info ti ON ti.id = tmi.team_info_id \n" +
            "\t\tWHERE\n" +
            "\t\t\tci.id = #{couId} \n" +
            "\t\t\tAND ti.id IS NOT NULL \n" +
            "\t\t) temp \n" +
            "\t) tag \n" +
            "FROM\n" +
            "\tcomprehensive_score coms \n" +
            "WHERE\n" +
            "\tcoms.course_id = #{couId} \n" +
            "\tAND coms.student_id IS NULL")
    int isScoreOfCouTeam(@Param("couId") Integer couId);

    /**
     * 查询本小组在该课程排名, 也可直接查询课程中所有小组排名
     * @param couId 课程id
     * @param teamId 小组id
     * @return 排名情况
     */
    @Select({"<script>", "SELECT\n" +
            "\t@rowno := @rowno + 1 AS rowno,\n" +
            "\tti.team_name,\n" +
            "\tcs.score \n" +
            "FROM\n" +
            "\tcomprehensive_score cs\n" +
            "\tLEFT JOIN team_info ti ON cs.team_id = ti.id,\n" +
            "\t( SELECT @rowno := 0 ) b \n" +
            "WHERE\n" +
            "\tcs.course_id = #{couId} \n" +
            "\tAND cs.student_id IS NULL\n" +
            "<if test=\"teamId != null\">AND cs.team_id = #{teamId}</if>\n" +
            "ORDER BY cs.score DESC", "</script>"})
    List<TeamRank> queryTeamRank(@Param("couId") Integer couId,
                                 @Param("teamId") Integer teamId);
}
